﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MSBIHelper.Helper;

using Microsoft.AnalysisServices;
using System.Text.RegularExpressions;
using System.IO;
using MSBIHelper.UI;

namespace MSBIHelper
{
    public partial class Frm_SSAS : Form
    {
        public Frm_SSAS()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                Cube _cube = GetCube(tb_connstr.Text, lb_db.Text, lb_cb.Text);
                if (_cube != null)
                {
                    DataTable dt = GetMDXSQL(_cube);
                    dataGridView1.DataSource = dt;
                    dataGridView1.Visible = true;
                    int totalwidth = dataGridView1.Width = dataGridView1.Columns[dataGridView1.Columns.Count - 1].Width;
                    dataGridView1.Columns[dataGridView1.Columns.Count - 1].Width = dataGridView1.Width - totalwidth;
                }
                else
                {
                    MessageBox.Show("not find available cube,please check!");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }
        private void AddInfo(string value,string type )
        { 
            rtbMsg.AppendText(string.Format("{0}:{1}",type,value));
            rtbMsg.AppendText(Environment.NewLine);
        }
        private void AddInfo(string str)
        { 
            rtbMsg.AppendText(str + Environment.NewLine); 
        }
        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                lb_db.DataSource = GetDbs(tb_connstr.Text);
            }
            catch (Exception ex )
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void lb_db_SelectedIndexChanged(object sender, EventArgs e)
        {
            lb_cb.DataSource = GetCubes(tb_connstr.Text, lb_db.Text);
        }
        private string GetDBConnStr()
        {
            Database _db = GetDB(GetServer(tb_connstr.Text), lb_db.SelectedValue.ToString());
            return _db != null ? (_db.DataSources[0] == null ? "" : _db.DataSources[0].ConnectionString) : "";
        }
        public string[]  GetDbs(string connstr)
        {
            List<string> lsdbs = new List<string>();
            Server _server = GetServer(connstr);
            foreach (Database db in _server.Databases)
            {
                lsdbs.Add(db.Name);
            }
            return lsdbs.ToArray<string>();
        }
        public string[]  GetCubes(string connstr, string dbname)
        {
            List<string> lscubes = new List<string>();
            Server _server = GetServer(connstr);
            Database _db = GetDB(_server, dbname);
            foreach (Cube _cb in _db.Cubes)
            {
                lscubes.Add(_cb.Name);
            }
            return lscubes.ToArray<string>();
        }
        public Server    GetServer(string connstr)
        {
            Server _server = new Server();
            _server.Connect(connstr);
            return _server;
        }
        public Database  GetDB(Server _server,string dbname)
        {
            return _server.Databases.FindByName(dbname);
        }
        public  Cube  GetCube()
        {
            //connect to the localhost 
            string connectstr = "Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;";
            Server _server = new Server();
            _server.Connect(connectstr);

            //Cube _cube = _server.Databases["ASDEMO"].Cubes.FindByName("Cube_AW");
            Cube _cube = _server.Databases[0].Cubes[0];
            return _cube;

        }
        public  Cube  GetCube(string connstr, string dbname, string _cubeName)
        {
            Cube _cube = null;
            Server _server = GetServer(connstr);
            if (_server.GetConnectionState(false).Equals(ConnectionState.Open))
            {
                Database _db = _server.Databases.FindByName(dbname);
                if (_db != null)
                    _cube = _db.Cubes.FindByName(_cubeName);
            }
            return _cube;
        }
        private Cube  GetCube(Database _db, string _cubeName)
        {
            return _db.Cubes.FindByName(_cubeName);
        }
        public string GetSourceQueryDefinition(Dimension dim,string tbname)
        { 
            string rst=tbname;
            DataSet ds = dim.DataSourceView.Schema;
            if(ds.Tables.Contains(tbname))
            {
                if(ds.Tables[tbname].ExtendedProperties.Contains("QueryDefinition"))
                    rst = "("+ds.Tables[tbname].ExtendedProperties["QueryDefinition"].ToString() + ") as " 
                        + RemoveTableSchema(ds,tbname).QuoteName();
                if (ds.Tables[tbname].ExtendedProperties["TableType"].ToString() == "Table")
                {
                    rst =
                        ds.Tables[tbname].ExtendedProperties["DbSchemaName"].ToString()
                        + "."
                        + ds.Tables[tbname].ExtendedProperties["DbTableName"].ToString()
                        + " AS " + tbname.Substring(ds.Tables[tbname].ExtendedProperties["DbSchemaName"].ToString().Length + 1).QuoteName();
                }
            }
            return rst;
        }
        public string GetColumnDefinition(DataSet ds, string columndatasource)
        {
            string columnDefinition = "";
            string tableName = columndatasource.Split('.')[0];
            string columnName = columndatasource.Split('.')[1];
            if (ds.Tables.Contains(tableName)
               && ds.Tables[tableName].Columns.Contains(columnName)
               && ds.Tables[tableName].Columns[columnName].ExtendedProperties.Contains("ComputedColumnExpression")
                )
            {
                columnDefinition = "(" + ds.Tables[tableName].Columns[columnName].ExtendedProperties["ComputedColumnExpression"].ToString() + ")";
                //修正列定义中可能含有不规范的列名 譬如说只是列名本身 不包含表名的情况 修正成tablename.columnName 怎么判断是否已经是这种形式？
                columnDefinition = ModifyColumn(columnDefinition, ds.Tables[tableName]);
            }
            else
            {
                columnDefinition = RemoveTableSchema(ds,columndatasource);
            }
            return columnDefinition;
        }
        public string ModifyColumn(string columndefinition, DataTable dt)
        {
            string newcoldef = string.Empty;
            newcoldef = columndefinition;
            string tbNameWithoutSchema = string.Empty;
            if (dt.ExtendedProperties["TableType"].ToString() == "Table") 
                 tbNameWithoutSchema = dt.TableName.Substring(dt.ExtendedProperties["DbSchemaName"].ToString().Length+1).QuoteName();
            else tbNameWithoutSchema = dt.TableName.QuoteName();

            foreach (DataColumn dc in dt.Columns)
            {
                //find the column name and the before and after is not A----Z
                int indexof = columndefinition.IndexOf(dc.ColumnName);
                newcoldef = UpdateColumn(newcoldef, dc.ColumnName,tbNameWithoutSchema);
            }
            return newcoldef;
        }
        private string UpdateColumn(string strsql, string colname , string tablename)
        {
            //update colname in strsql in strsql with tablename.colname
            string strnew = strsql;
            if (strsql.IndexOf(colname) == -1) return strsql;
            int index = strnew.IndexOf(colname);
            while (index > -1)
            {
                if (index == 0 && strnew.Length - 1 > colname.Length && IsNonChar(strnew.Substring(colname.Length, 1)[0])) //find
                    strnew = tablename + "." + strsql;
                if (index + colname.Length == strnew.Length && index != 0 && IsNonChar(strnew.Substring(index - 1, 1)[0]))
                    strnew = strnew.Substring(0, index) + tablename + "." + strnew.Substring(index);
                if (index > 0 && index < strnew.Length && IsNonChar(strnew.Substring(index - 1, 1)[0]) && IsNonChar(strnew.Substring(index + colname.Length, 1)[0]))
                    strnew = strnew.Substring(0, index) + tablename + "." + strnew.Substring(index);
                index = strnew.IndexOf(colname,index+tablename.Length+colname.Length);
            }
            return strnew;
        }
        public bool IsNonChar(char c)
        {
            if (c <= 'z' && c >= 'a') return false;
            if (c >= 'A' && c > 'A') return false;
            return true;
        }
        private const string mdxtemplate = "SELECT {0} ON 0 \r\n,NON EMPTY {1} ON 1 \r\n FROM {2} CELL PROPERTIES VALUE";
        StringBuilder sbsql = new StringBuilder("SELECT \r\n{$GROUPBY}\r\n");
        public DataTable GetMDXSQL(Cube _cube)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Dimension", typeof(String));
            dt.Columns.Add("Metric", typeof(String));
            dt.Columns.Add("MDX", typeof(String));
            dt.Columns.Add("TSQL",typeof(String));
            
            string mdx = ""; 
            string tsql = "";
            string metric = "";
            string strdim = "";

            StringBuilder sbMeasures = new StringBuilder("{");
            foreach (MeasureGroup mg in _cube.MeasureGroups)
            {
                #region Measure
                foreach (Measure m in mg.Measures)
                {
                    #region Metrics
                    if (m.AggregateFunction.Equals(AggregationFunction.Sum)
                        ||
                        m.AggregateFunction.Equals(AggregationFunction.Count)
                        ||
                        m.AggregateFunction.Equals(AggregationFunction.DistinctCount)
                        ||
                        m.AggregateFunction.Equals(AggregationFunction.Max)
                        ||
                        m.AggregateFunction.Equals(AggregationFunction.Min)
                        )
                    {
                        metric = mg.Name.QuoteName() + "." + m.Name.QuoteName();
                        sbMeasures.Append(m.Name.QuoteName().AppendComma());
                        if (m.AggregateFunction.Equals(AggregationFunction.Sum))
                            sbsql.Append(
                                AggregationFunction.Sum.ToString().AppendLeftParenthesis()
                                + 
                                (m.Source.ToString().Split('.').Length > 0 ?
                                m.Source.ToString().Split('.')[1]:
                                m.Source.ToString().Split('.')[0])
                                .AppendRightParenthesis()
                                + " AS "
                                + m.Name.QuoteName().AppendComma().AppendNewLine()
                                );
                        if (m.AggregateFunction.Equals(AggregationFunction.Count))
                            sbsql.Append("COUNT(*) AS "
                            + m.Name.QuoteName().AppendComma().AppendNewLine()
                            );
                        if (m.AggregateFunction.Equals(AggregationFunction.DistinctCount))
                            sbsql.Append(
                              "COUNT(Distinct "
                               + (m.Source.ToString().Split('.').Length > 0 ?
                                m.Source.ToString().Split('.')[1] :
                                m.Source.ToString().Split('.')[0])
                               .AppendRightParenthesis()
                               + " AS "
                               + m.Name.QuoteName().AppendComma().AppendNewLine()
                               );
                        if (m.AggregateFunction.Equals(AggregationFunction.Max))
                            sbsql.Append(
                               AggregationFunction.Max.ToString().AppendLeftParenthesis()
                               + (m.Source.ToString().Split('.').Length > 0 ?
                                m.Source.ToString().Split('.')[1] :
                                m.Source.ToString().Split('.')[0])
                               .AppendRightParenthesis()
                               + " AS "
                               + m.Name.QuoteName().AppendComma().AppendNewLine()
                               );
                        if (m.AggregateFunction.Equals(AggregationFunction.Min))
                            sbsql.Append(
                               AggregationFunction.Min.ToString().AppendLeftParenthesis()
                               + (m.Source.ToString().Split('.').Length > 0 ?
                                m.Source.ToString().Split('.')[1] :
                                m.Source.ToString().Split('.')[0])
                               .AppendRightParenthesis()
                               + " AS "
                               + m.Name.QuoteName().AppendComma().AppendNewLine()
                               );
                    #endregion

                        sbMeasures.Remove(sbMeasures.Length - 1, 1);//remove the last coma
                        sbMeasures.Append("}");
                        sbsql.Remove(sbsql.Length - 3, 3);//remove ,\r\n

                        HashSet<string> selectedDims = GetSelectedDimensions();
                        #region MeasureGroupDimension
                        foreach (MeasureGroupDimension mgd in mg.Dimensions)
                        {
                            if (!selectedDims.Contains(mgd.Dimension.Name)) continue;

                            string _facttable = string.Empty;
                            string _dimensiontable = string.Empty; 
                            string origindimtable = string.Empty;
                            string _joinby = "ON ";
                            string _groupby = "GROUP BY ";

                            if (!mgd.GetType().Name.Equals("RegularMeasureGroupDimension"))
                                continue;

                            foreach (MeasureGroupAttribute mga in ((RegularMeasureGroupDimension)mgd).Attributes)
                            {
                                if (mga.Type != MeasureGroupAttributeType.Granularity) continue;
                                origindimtable = mga.Attribute.KeyColumns[0].Source.ToString().Split('.')[0].ReplaceFirstUnderLineWithDot();
                                
                                _dimensiontable = GetSourceQueryDefinition(mgd.CubeDimension.Dimension, mga.Attribute.KeyColumns[0].Source.ToString().Split('.')[0]);
                                _facttable = GetSourceQueryDefinition(mgd.CubeDimension.Dimension, mga.KeyColumns[0].Source.ToString().Split('.')[0]);

                                for (int i = 0; i < mga.KeyColumns.Count; i++)
                                {
                                    _joinby += RemoveTableSchema(mga.Parent.Dimension.DataSourceView.Schema, mga.Attribute.KeyColumns[i].Source.ToString());
                                    _joinby += " = " + RemoveTableSchema(mga.Parent.Dimension.DataSourceView.Schema,mga.KeyColumns[i].Source.ToString()).Append(" AND ");
                                }
                                break;
                            }
                            _joinby = _joinby.TrimEnd(" AND ".ToCharArray());
                            #region Hierarchy
                            foreach (Hierarchy h in mgd.CubeDimension.Dimension.Hierarchies)
                            {
                                foreach (Level l in h.Levels)
                                {
                                    mdx =
                                     string.Format(mdxtemplate, sbMeasures.ToString().TrimEnd(','),
                                     //h.Parent.Name
                                     mgd.CubeDimension.Name
                                     .QuoteName().Append(".").Append(h.Name.QuoteName())
                                     .Append(".").Append(l.Name.QuoteName()),
                                    _cube.Name.QuoteName());

                                    string _groupByInSelect = "";
                                    foreach (DataItem di in l.SourceAttribute.KeyColumns)
                                    {
                                        _groupby += GetColumnDefinition(l.Parent.Parent.DataSourceView.Schema, di.Source.ToString()).AppendComma();
                                    }
                                    //if the key column count >1 or key column not equal name column
                                    //Then add the name column to group by in tsql statement
                                    if (l.SourceAttribute.KeyColumns.Count > 1
                                        ||
                                        (l.SourceAttribute.KeyColumns.Count.Equals(1)
                                        &&
                                        !l.SourceAttribute.KeyColumns[0].Source.ToString().Equals(l.SourceAttribute.NameColumn.Source.ToString()))
                                        )
                                    {
                                        _groupByInSelect = GetColumnDefinition(l.Parent.Parent.DataSourceView.Schema, l.SourceAttribute.NameColumn.Source.ToString());
                                        if (_groupby.IndexOf(_groupByInSelect) == -1)
                                            _groupby += "\r\n" + _groupByInSelect;

                                        _groupByInSelect += " AS ";
                                        _groupByInSelect += l.SourceAttribute.NameColumn.Source.ToString().Split('.')[1].QuoteName().AppendComma();
                                    }
                                    else _groupByInSelect = _groupby.Substring("GROUP BY ".Length);
                                    _groupby = _groupby.TrimEnd(',');
                                    //if the _facttable == _dimensiontable then give a alias for dimtable
                                    if (_facttable.Equals(_dimensiontable))
                                    {
                                        _dimensiontable += "2";
                                        _groupby = _groupby.Replace(origindimtable, origindimtable + "2");
                                        _groupByInSelect = _groupByInSelect.Replace(origindimtable, origindimtable + "2");
                                    }

                                    tsql =
                                        sbsql.ToString()
                                        .Replace("{$GROUPBY}", _groupByInSelect)
                                        .AppendNewLine()
                                        .Append("FROM ")
                                        .Append(_facttable)
                                        .AppendNewLine()
                                        .Append("JOIN ")
                                        .Append(_dimensiontable)
                                        .AppendNewLine()
                                        .Append(_joinby)
                                        .AppendNewLine();

                                    string morejoin = TableJoin(l.SourceAttribute);
                                    tsql = tsql.Append(morejoin);
                                    tsql = tsql.Append(_groupby);
                                    _groupby = "GROUP BY ";
                                    //Add rows for dt
                                    strdim = mgd.CubeDimension.Name.QuoteName() + "." + l.Name.QuoteName();
                                    AddRow(dt, strdim, metric, mdx, tsql);
                                }
                            }
                            #endregion

                            #region Attributes
                            if (mgd.CubeDimension.Dimension.Hierarchies.Count == 0)
                            {
                                foreach (DimensionAttribute da in mgd.CubeDimension.Dimension.Attributes)
                                {
                                    string _groupByInSelect = "";
                                    if (da.AttributeHierarchyEnabled == true)
                                    {
                                        mdx = string.Format(mdxtemplate, sbMeasures.ToString().TrimEnd(','),
                                            mgd.CubeDimension.Name
                                            .QuoteName().Append(".").Append(
                                            da.Name.QuoteName())
                                            , _cube.Name.QuoteName());
                                    }
                                    else continue;
                                    foreach (DataItem di in da.KeyColumns)
                                    {
                                        _groupby += GetColumnDefinition(da.Parent.DataSourceView.Schema, di.Source.ToString()).AppendComma();
                                    }
                                    //if the key column count >1 or key column not equal name column
                                    //Then add the name column to group by in tsql statement
                                    if (da.KeyColumns.Count > 1
                                        ||
                                        (da.KeyColumns.Count.Equals(1)
                                        &&
                                        !da.KeyColumns[0].Source.ToString().Equals(da.NameColumn.Source.ToString()))
                                        )
                                    {
                                        _groupByInSelect = GetColumnDefinition(da.Parent.DataSourceView.Schema, da.NameColumn.Source.ToString());
                                        if (_groupby.IndexOf(_groupByInSelect) == -1)
                                            _groupby += "\r\n" + _groupByInSelect;

                                        //_groupByInSelect += " AS " + 
                                        //    RemoveTableSchema( 
                                        //    da.Parent.DataSourceView.Schema,
                                        //    da.NameColumn.Source.ToString()
                                        //    )
                                        //    .Replace(".", "").AppendComma();

                                        _groupByInSelect += " AS ";
                                        _groupByInSelect += da.NameColumn.Source.ToString().Split('.')[1].QuoteName().AppendComma();

                                    }
                                    else _groupByInSelect = _groupby.TrimStart("GROUP BY ".ToCharArray());
                                    _groupby = _groupby.TrimEnd(',');
                                    //if the _facttable == _dimensiontable then give a alias for dimtable
                                    if (_facttable.Equals(_dimensiontable))
                                    {
                                        _dimensiontable += "2";
                                        _groupby = _groupby.Replace(origindimtable, origindimtable + "2");
                                        _groupByInSelect = _groupByInSelect.Replace(origindimtable, origindimtable + "2");
                                    }

                                    tsql=
                                        sbsql.ToString()
                                        .Replace("{$GROUPBY}", _groupByInSelect)
                                        .AppendNewLine()
                                        .Append("FROM ")
                                        .Append(_facttable)
                                        .AppendNewLine()
                                        .Append("JOIN ")
                                        .Append(_dimensiontable)
                                        .AppendNewLine()
                                        .Append(_joinby)
                                        .AppendNewLine();
                                    string morejoin = TableJoin(da);
                                    tsql = tsql.Append(morejoin);
                                    tsql = tsql.Append(_groupby);
                                    _groupby = "GROUP BY ";
                                    strdim = mgd.CubeDimension.Name.QuoteName() + "." + da.Name.QuoteName();
                                    AddRow(dt, strdim, metric, mdx, tsql);
                                }
                            }
                            #endregion

                        }
                        #endregion

                        sbsql = new StringBuilder("SELECT \r\n{$GROUPBY}\r\n");
                        sbMeasures = new StringBuilder("{");
                    }
                }

                #endregion
            }

            return dt;
        }
        public void AddRow(DataTable dt, string strdim, string metric, string mdx, string tsql)
        {
            DataRow dr = dt.NewRow();
            dr[0] = strdim; 
            dr[1] = metric;
            dr[2] = mdx;
            dr[3] = tsql;
            dt.Rows.Add(dr);
        }
        private string RemoveTableSchema(DataSet ds,string disrc)
        {
            string tbname = disrc.Split('.')[0];
            string colname =disrc.Split('.').Length>1?disrc.Split('.')[1]:"";
            if (ds.Tables.Contains(tbname)
              && ds.Tables[tbname].ExtendedProperties["TableType"].ToString() == "Table"
                )
            {
                disrc = disrc.Substring(ds.Tables[tbname].ExtendedProperties["DbSchemaName"].ToString().Length + 1);
                if (colname != "")
                    disrc = disrc.Split('.')[0].QuoteName() + "." + disrc.Split('.')[1];
            }
            else
            {
                if(colname!="")
                    disrc = tbname.QuoteName() + "."+ colname;
            }
            return disrc;
        }
     
        private string TableJoin(DimensionAttribute da)
        {
            //根据l找到sourcetable
            //然后去匹配tablelinks中的长度 然后再去获取
            string morejoin = "";
            string tablelinks = GetTableLinks(da.Parent);
            if (tablelinks.Split(',').Length<3) return "";
            string TableName = da.KeyColumns[0].Source.ToString().Split('.')[0]+",";
            if (tablelinks.IndexOf(TableName).Equals(0)) return "";
            string strUsedTables = tablelinks.Substring(0, tablelinks.IndexOf(TableName)-1);
            List<string> usedTables = strUsedTables.Split(',').ToList<string>();
            usedTables.Remove("");

            DataSet ds = da.Parent.DataSourceView.Schema;
            for (int i = 0; i < ds.Relations.Count && usedTables.Count > 0; i++)
            {
                string childTable = usedTables[0];
                DataRelation dr = ds.Relations[i];
                if (dr.ChildTable.TableName.Equals(childTable))
                {
                    morejoin = morejoin.Append("JOIN\r\n" 
                         + GetSourceQueryDefinition(da.Parent,dr.ParentTable.TableName)
                        ).AppendNewLine().Append("ON ");
                    for (int j = 0; j < dr.ChildColumns.Length; j++)
                    {
                        morejoin = morejoin.Append(
                            RemoveTableSchema(
                            dr.ParentTable.DataSet,
                            dr.ParentTable.TableName
                            )
                            +"." + dr.ParentColumns[j].ColumnName + " = " +
                            RemoveTableSchema(
                            dr.ChildTable.DataSet,
                            dr.ChildTable.TableName
                            )
                            +"." + dr.ChildColumns[j].ColumnName
                            ).AppendNewLine();
                    }
                    usedTables.RemoveAt(0);
                    i = 0;
                }
            }

            return morejoin;
        }
        private string GetTableLinks(Dimension dim)
        {
            HashSet<string> tables = new HashSet<string>();
            string  MainTable =  "";
            string  tablelinks = string.Empty;
            DataSet ds = dim.DataSourceView.Schema;
            MainTable= dim.KeyAttribute.KeyColumns[0].Source.ToString().Split('.')[0];
            tablelinks = tablelinks.Append(MainTable+",");
            for (int i = 0; i < ds.Relations.Count; i++ )
            {
                DataRelation dr = ds.Relations[i];
                if (dr.ChildTable.TableName.Equals(MainTable))
                {
                    tablelinks = tablelinks.Append(dr.ParentTable.TableName + ",");
                    MainTable = dr.ParentTable.TableName;
                    if (tables.Contains(MainTable))
                        break;
                    else
                        tables.Add(MainTable);
                    i = 0;
                }
            }
            return tablelinks;
        }
        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                TestDBDimension();

                //TestDataRelation();
                //TestDsvTableProperties();
                //TestDataRelation2();
                //GetSelectedDimensions(); 
                //string strsql= "GROUP BY USER_D.BUS_RGN_NM";
                //rtbMsg.AppendText(strsql.TrimStart("GROUP BY ".ToCharArray()));

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void TestDsvTableProperties()
        {
            //Get the definition of the DSV
            Server _server = GetServer(tb_connstr.Text);
            Database _db = null;
            foreach (Database db in _server.Databases)
            {
                if (db.Name.Equals(lb_db.Text, StringComparison.CurrentCultureIgnoreCase))
                {
                    _db = db;
                    break;
                }
            }
            //get the dsv of the database
            DataSet ds = _db.DataSourceViews[0].Schema;

            foreach (DataTable dt in ds.Tables)
            {
                if(!dt.TableName.Equals("dbo_DimTime")) continue;
                //dt.ChildRelations[0].ChildColumns[0]
                AddInfo("tablename:" + dt.TableName);
                foreach (System.Collections.DictionaryEntry de in dt.ExtendedProperties)
                {
                    AddInfo("PropertyName:" + de.Key + " Value:" + de.Value.ToString());
                }

                foreach (DataColumn dc in dt.Columns)
                {
                    AddInfo("colname:" + dc.ColumnName);
                    if (dc.ExtendedProperties.Contains("ComputedColumnExpression"))
                        AddInfo("compute colume expression:" + dc.ExtendedProperties["ComputedColumnExpression"]);
                }
            }
        }
        private void TestMGDimensionInfo()
        {
            Cube _cube = GetCube();
            rtbMsg.AppendText("CubeName:" + _cube.Name + Environment.NewLine);
            foreach (MeasureGroup mg in _cube.MeasureGroups)
            {
                if (!mg.Name.Equals("Internet Sales")) continue;
                //AddInfo(mg.Name, "measuregroupName");
                //foreach (Measure m in mg.Measures)
                //{
                //    AddInfo(m.Name, "measureName");
                //    AddInfo(m.Parent.Name, "parentName");
                //}

                foreach (MeasureGroupDimension mgd in mg.Dimensions)
                {
                    AddInfo(mgd.GetType().Name, "dimensiontype");
                    AddInfo(mgd.CubeDimension.Name, "dimensionName");
                    if (mgd.CubeDimension.Dimension.Name != "Date") continue;
                    foreach (Hierarchy h in mgd.CubeDimension.Dimension.Hierarchies)
                    {
                        AddInfo(h.Name, "HierarchyName");
                        foreach (Level l in h.Levels)
                        {
                            foreach (DataItem di in l.SourceAttribute.KeyColumns)
                            {
                                AddInfo(di.Source.ToString());

                            }
                        }
                    }
                }
            }
        }
        private void TestDBDimension()
        { 
            Server _server = GetServer(tb_connstr.Text);
            Database _db = null;
            _db =_server.Databases.FindByName(lb_db.Text);
            if (_db == null) { AddInfo("database is not found"); return; }
            foreach (Dimension dim in _db.Dimensions)
            {
               //if (!dim.Name.Equals("Date")) continue;
               Dictionary<string, string> dict = new Dictionary<string, string>();
               //GetTables(dim, dict);
               //AddInfo("Table links:" + GetTableLinks(dim));
                
               AddInfo("***********************dimension Name:" + dim.Name+" Start*************************************");
               AddInfo("dim.ID:" + dim.ID);
               AddInfo("dim.DataSource:" + dim.Source);
               AddInfo("dim.DataSourceView:" + dim.DataSourceView);
               foreach (DimensionAttribute da in dim.Attributes)
               {
                   AddInfo("AttributeName:" + da.Name);
                   AddInfo("AttributeHierarchyEnabled:" + da.AttributeHierarchyEnabled);
                   AddInfo("AttributeHierarchyVisible:" + da.AttributeHierarchyVisible);
                   AddInfo("NameColumn:" + da.NameColumn);
                   AddInfo("ValueColumn:" + da.ValueColumn);
                   foreach (DataItem di in da.KeyColumns)
                   {
                       AddInfo("KeyColumns Source:" + di.Source);
                   }

               }
               AddInfo("***********************dimension Name:" + dim.Name+" End***************************************");
            }
            

             
        }
        private void TestDataSource()
        {
            Server _server = GetServer(tb_connstr.Text);
            Database _db = null;
            _db = _server.Databases.FindByName(lb_db.Text);
            if (_db != null)
            { 
                //find the datasource connection string of this db
                //DataSourceConnectionString = _db.DataSources[0].ConnectionString;
            }
        }
        private void lb_cb_SelectedIndexChanged(object sender, EventArgs e)
        {
            ((ListBox)cbl_dims).DataSource = GetDimensions();
           
        }
        private HashSet<string> GetSelectedDimensions()
        {
            HashSet<string> hsSelected = new HashSet<string>();
            for (int i = 0; i < cbl_dims.Items.Count; i++)
                if (cbl_dims.GetItemChecked(i))
                     hsSelected.Add(cbl_dims.GetItemText(cbl_dims.Items[i]));
            return hsSelected;
 
        }
        private string[] GetDimensions()
        {
            HashSet<string> hsdims = new HashSet<string>();
            Server _server = GetServer(tb_connstr.Text);
            Database _db = GetDB(_server, lb_db.Text);
            Cube _cube = null;
            if (_db != null)
                _cube = GetCube(_db, lb_cb.Text);

            foreach (CubeDimension cd in _cube.Dimensions)
            {
                hsdims.Add(cd.Dimension.Name);
            }
            return hsdims.ToArray() ;
        }
        private void button4_Click(object sender, EventArgs e)
        {
            StringBuilder sbsql = new StringBuilder(10000);
            DataTable dt = (DataTable)dataGridView1.DataSource;
            foreach (DataRow dr in dt.Rows)
            {
                sbsql.AppendLine(dr[1].ToString());
            }
            dataGridView1.Visible = false;
            rtbMsg.Text = sbsql.ToString();
        }

        private void copyCurrentCellToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataObject dataObj = dataGridView1.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);
        }
         
        private void selectAllRowsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            dataGridView1.SelectAll();
            DataObject dataObj = dataGridView1.GetClipboardContent();
            if (dataObj != null)
                Clipboard.SetDataObject(dataObj);
        }

        private void clearSelectedToolStripMenuItem_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow dgvr in this.dataGridView1.Rows)
            {
                dgvr.Selected = false;
            }
        }

        private void export2CSVToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //Let user choose the directory for dump csv
            FolderBrowserDialog fbd = new FolderBrowserDialog();
            if (fbd.ShowDialog() == DialogResult.OK)
            {
                string folder = fbd.SelectedPath;
                string csvfilename = fbd.SelectedPath + "\\" +
                                     string.Format("SSASTestCases_{0}.csv", DateTime.Now.ToString("yyyyMMddhhmmss")) ;

                try
                {
                    if (File.Exists(csvfilename)) File.Delete(csvfilename);

                    using (StreamWriter writer = new StreamWriter(csvfilename))
                    {
                        RfcWriter.WriteDataTable(dataGridView1.DataSource as DataTable, writer, true);
                    }

                    Util.RunCmd(folder);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox1.Checked)
            {
                tb_connstr.Text = "Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;";;
            }
            else
            {
                tb_connstr.Text = "Provider = SQLOLEDB; Data Source = CLQR-OLAP-ITG.austin.hp.com; Integrated Security = SSPI"; ;
            }
        }

        private void executeMDXToolStripMenuItem_Click(object sender, EventArgs e)
        {
          string mdx = dataGridView1.CurrentCell.Value.ToString();
          string connectstring = tb_connstr.Text;
          string type = "MDX";
          Dictionary<string, string> dict = new Dictionary<string, string>();
          dict.Add("TYPE", type);
          dict.Add("ConnectionString", connectstring);
          dict.Add("MDX", mdx);
          Frm_Executes frm_Execute = (Frm_Executes)FormFactory.Create(typeof(Frm_Executes));
          frm_Execute.MdiParent = this.MdiParent;
          frm_Execute.Show();
          frm_Execute.Activate();
          MidModule.SendMessage(this,dict);
        }

        private void executeSQLToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string sql = dataGridView1.CurrentCell.Value.ToString();
            string connectstring = GetDBConnStr();
            string type = "SQL";
            Dictionary<string, string> dict = new Dictionary<string, string>();
            dict.Add("TYPE", type);
            dict.Add("ConnectionString", connectstring);
            dict.Add("SQL", sql);
            Frm_Executes frm_Execute = (Frm_Executes)FormFactory.Create(typeof(Frm_Executes));
            frm_Execute.MdiParent = this.MdiParent;
            frm_Execute.Show();
            frm_Execute.Activate();
            MidModule.SendMessage(this,dict);
        }

        private void Frm_SSAS_Load(object sender, EventArgs e)
        {
            //Append info for the operations
            StringBuilder sbinfo = new StringBuilder(1024);
            sbinfo.AppendLine("Step1: please modify the olap connect string for your need!  then click the LoadMetaData button!");
            sbinfo.AppendLine("Step2: Choose the database and dimensions you want to check(test)! then click the GenerateMDXSQL button!");
            sbinfo.AppendLine("Step3: you can export the content to csv file and copy with the right click!");
            rtbMsg.AppendText(sbinfo.ToString());

            this.rtbMsg.SelectAll();
            this.rtbMsg.SelectionColor = Color.Purple;
            Font font = new Font(FontFamily.GenericMonospace, 14, FontStyle.Bold);
            this.rtbMsg.SelectionFont = font;
            
        }

        private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {

            if (this.dataGridView1.Rows.Count != 0)
            {
                Font oldfont = dataGridView1.Font;
                dataGridView1.RowsDefaultCellStyle.Font = new Font("Gabriola",14, FontStyle.Bold);
                Color [] mycolor = new Color[7];
                mycolor[0] = Color.Navy;
                mycolor[1] = Color.Orange;
                mycolor[2] = Color.Olive;
                mycolor[3] = Color.Green;
                mycolor[4] = Color.Orchid;
                mycolor[5] = Color.Maroon;
                mycolor[6] = Color.Purple;

                for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
                {
                    this.dataGridView1.Rows[i].DefaultCellStyle.ForeColor = mycolor[i%7];
                 
                }
            }
        }

    }
}
